import json
import sqlite3
import urllib.request

def sqlinit():
    conn = sqlite3.connect('1W+ans.db')
    sqlcursor = conn.cursor()
    sqlcursor.execute('''create table answers(
        answerid integer primany key auto_increment,
        title   char(100),
        ispost  integer,
        link    char(100),
        agree   integer,
        date    datetime
    );''')

    return conn, sqlcursor

if __name__ == "__main__":
    conn, sqlcursor = sqlinit()

    for page in range(10):
        rankapi = ''.join(['http://api.kanzhihu.com/topuser/agree/', str(page + 1), '/50'])
        rankresp = urllib.request.urlopen(rankapi)
        rankjson = rankresp.read().decode('utf8')
        users = json.loads(rankjson)['topuser']
        for user in users:
            userapi = ''.join(['http://api.kanzhihu.com/userdetail2/', user['hash']])
            userresp = urllib.request.urlopen(userapi)
            userjson = userresp.read().decode('utf8')
            user = json.loads(userjson)
            answers = user['topanswers']
            for answer in answers:
                if int(answer['agree']) > 9500:
                    print(answer)
                    data = ''.join(['insert into answers (title, ispost, link, agree, date) values ("', answer['title'], '", ', answer['ispost'], ', "', ''.join(['http://www.zhihu.com', answer['link']]), '", ', answer['agree'], ', "', answer['date'], '");'])
                    print(data)
                    sqlcursor.execute(data)
                else:
                    break

    conn.commit()
    sqlcursor.close()
    conn.close()
